package nl.hu.verbrandendephoenix.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import nl.hu.verbrandendephoenix.shared.Auction;

public class AuctionMethods {
	public static Auction getAuctionData(int auctionID, Connection con) {
		Auction a = null;
		int auctionid = 0;
		String auctionname = "";
		String auctiondescr = "";
		int price = 0;
		String category = "";
		int owner = 0;
		Date startdate = null;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from Auction WHERE AuctionID = '" + auctionID + "'" );
			while(rs.next())
			{
				auctionid = rs.getInt("AUCTIONID");
				auctionname = rs.getString("NAME");
				auctiondescr = rs.getString("DESCRIPTION");
				price = rs.getInt("MINOFFER");
				category = rs.getString("CATEGORY");
				owner = rs.getInt("USERID");
				startdate = rs.getDate("STARTDATE");
			}
			stmt.close();
			con.close();
			a = new Auction(auctionname, category, price, auctiondescr, owner, startdate);
			a.setAuctionID(auctionid);
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return a;
	}

	public static boolean createAuction(String name, String desc, int minBid, String userkey, String cat, Connection con) {
		/* 
		 * INSERT INTO "AUCTION" 
		 * (AUCTIONID, NAME, DESCRIPTION, MINOFFER, STARTDATE, CATEGORY, USERID, ACTIVE) 
		 * VALUES 
		 * ('getID++;', 'name', 'desc', 'minBid', TO_DATE('getDate(today);', 'DD-MM-RR'), 'cat', 'userID', '1')
		 */

		int uid = UserMethods.getUserID(userkey, con);

		try {
			String query = "INSERT INTO AUCTION (NAME, DESCRIPTION, MINOFFER, CATEGORY, USERID, ACTIVE) VALUES (" +
					"?, " +
					"?, " +
					"?, " +
					"?, " +
					"?, " +
					"?)";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, desc);
			ps.setInt(3, minBid);
			ps.setString(4, cat);
			ps.setInt(5, uid);
			ps.setInt(6, 1);
			ps.executeUpdate();
			ps.close();
			con.close();
			return true;
		} catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public static ArrayList<Auction> searchAuction(String aucN, String minPrice, String maxPrice, String cat, Connection con) {
		ArrayList<Auction> auctions = new ArrayList<Auction>();

		String productName = "";
		String description = "";
		int price = 0;
		String category = "";
		int userID = 0;
		Date date = null;
		int auctionID = 0;

		String aucNStatement = "";
		String priceStatement = "";
		String minPriceStatement = "";
		String maxPriceStatement = "";
		String catStatement = " WHERE Category LIKE '" + cat + "'";

		if(!aucN.equals(""))
			aucNStatement = " AND Name LIKE '%" + aucN + "%'";

		if(!minPrice.equals("") && maxPrice.equals(""))
			minPriceStatement = " AND Minoffer >= " + minPrice + "";

		if(minPrice.equals("") && !maxPrice.equals(""))
			maxPriceStatement = " AND Minoffer <= " + maxPrice + "";

		if(!minPrice.equals("") && !maxPrice.equals(""))
			priceStatement = " AND Minoffer BETWEEN " + minPrice + " AND " + maxPrice;

		if(cat.equals("Alles"))
			catStatement = " WHERE Category LIKE '%'";

		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from Auction" + catStatement + aucNStatement + minPriceStatement + maxPriceStatement + " AND Active = 1" + priceStatement);
			while(rs.next())
			{
				auctionID = rs.getInt("AUCTIONID");
				productName = rs.getString("NAME");
				description = rs.getString("DESCRIPTION");
				price = rs.getInt("MINOFFER");
				category = rs.getString("CATEGORY");
				userID = rs.getInt("USERID");
				date = rs.getDate("STARTDATE");

				Auction a = new Auction(productName, category, price, description, userID, date);
				a.setAuctionID(auctionID);

				auctions.add(a);
			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}

		return auctions;
	}
	public static ArrayList<Auction> getMyAuctions(String LoginKey, Connection con) {
		ArrayList<Auction> auctions = new ArrayList<Auction>();

		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT name, auctionid from Auction Where userid = " + UserMethods.getUserID(LoginKey, con));

			while(rs.next())
			{
				auctions.add(new Auction(rs.getString("name"), rs.getInt("auctionid")));

			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}

		return auctions;
	}

	public static int getAuctionOwnerID(int acID, Connection con){
		int ownerID = 0;

		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT userid from auction where auctionid = " + acID);
			if(rs.next()){
				ownerID = rs.getInt("USERID");
			}
			stmt.close();
			con.close();

		}

		catch(SQLException ex){
			ex.printStackTrace();
		}
		return ownerID;

	}

	public static boolean compareAuctionOwner(int acID, String LoginKey, Connection con){
		int userID = UserMethods.getUserID(LoginKey, con);
		int ownerID; 
		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT userid from auction where auctionid = " + acID);

			if(rs.next()){
				ownerID = rs.getInt("USERID");
			}else{
				return false;
			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
		return userID == ownerID;

	}

	public static boolean editAuction(int acID, Auction a, Connection con){

		try{
			String query = "Update Auction set description=?, " + "category =?" + "Where auctionID = ?";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, a.getDescription());
			ps.setString(2, a.getCategory());
			ps.setInt(3, a.getAuctionID());

			ps.executeUpdate();
			ps.close();
			con.close();
			return true;

		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public static Boolean deleteAuction(int acID, Connection con){
		try{
			Statement stmt = con.createStatement();
			stmt.executeQuery("delete from auction where auctionid = " + acID);
			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("delete from bids where auctionid = " + acID);
			return true;

		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public static Boolean checkCredits (int acID, String LoginKey, Connection con){
		int userID = UserMethods.getUserID(LoginKey, con); 
		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT credits from User where userid = " + userID);
			int usCred = rs.getInt("credits");

			Statement stmt2 = con.createStatement();
			ResultSet rs2 = stmt2.executeQuery("SELECT price from Auction where auctionid = " + acID);
			int acCred = rs2.getInt("price");


			if(usCred <= acCred){
				return true;
			}else{
				return false;
			}


		}catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static Boolean payCredits (int acID, String LoginKey, Connection con){
		int auctionOwner = getAuctionOwnerID(acID, con);
		con = ServerServiceImpl.getConnection();
		int userID = 0;
		int bid = 0;
		int userCred = 0;
		int auctionOwnerCred = 0;

		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT userid, max(price) from bids where auctionid = " + acID + " group by userid");

			while(rs.next()){
				bid = rs.getInt("max(price)");
				userID = rs.getInt("userid");
			}

			rs = stmt.executeQuery("SELECT credits from gebruiker where userid = " + userID);

			if(rs.next()){
				userCred = rs.getInt("credits");
			}

			rs = stmt.executeQuery("SELECT credits from gebruiker where userid = " + auctionOwner);

			if(rs.next()){
				auctionOwnerCred = rs.getInt("credits");
			}

			stmt.close();

			int nwUserCred = userCred - bid;
			int nwAuctionOwnerCred = auctionOwnerCred + bid;

			String query = "Update gebruiker set credits = ? where userid = ?";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, nwUserCred);
			ps.setInt(2, userID);

			ps.executeUpdate();

			ps.setInt(1, nwAuctionOwnerCred);
			ps.setInt(2, auctionOwner);

			ps.executeUpdate();
			ps.close();
			con.close();

			stopAuction(acID, ServerServiceImpl.getConnection());

			return true;
		}catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static Boolean addCredits(String LoginKey, int aantCreds, Connection con){
		int userID = UserMethods.getUserID(LoginKey, con);
		int oudCreds;
		int nwCreds;
		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT credits from gebruiker where userid = " + userID);
			if(rs.next()){
				oudCreds = rs.getInt("credits");
				nwCreds = oudCreds + aantCreds;
			}else{
				nwCreds = 0;

			}

			String query = "Update gebruiker set credits = ? where userid = ?";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, nwCreds);
			ps.setInt(2, userID);
			ps.executeUpdate();
			ps.close();
			con.close();

			return true;
		}catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static Boolean stopAuction(int acID, Connection con){
		try{
			Statement stmt = con.createStatement();
			stmt.executeQuery("update auction set active = 0 where auctionid = " + acID);
			return true;
		}catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
	}

	public static boolean checkActive(int acID, Connection con) {
		int active = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select active from auction where auctionid = " + acID);
			if(rs.next()) {
				active = rs.getInt("active");
			}
		}
		catch(SQLException ex){
			ex.printStackTrace();
			return false;
		}
		if(active == 0)
			return false;
		return true;
	}
}